from rest_framework.viewsets import ViewSet
from rest_framework.decorators import action

from main.report.models import ReportInterface, InterfaceFields, PlatformInfo,ModuleInfo,ReportInfo,DataSource,DataTable,TableMapping,TableColumn
from main.system.models import User 
from main.utils.util_response import DetailResponse
from main.utils.util_db import MySqlHandler
import main.migrates.sql as table_sql

from django.db.models import Q,F

handler = MySqlHandler('etlctl_test')
class MigrateViewSet(ViewSet):

    @action(detail=False, methods=['post'],url_path='userList')
    def user_list(self, request):
        items = handler.query("select user_id,login_name, user_name,user_passwd as password, status, email from etlctl.etl_user")
        # items = User.objects.raw("select user_id as id,user_id,login_name, user_name, email, update_time from etlctl.etl_user", using="etlctl_test")
        user_list = User.objects.bulk_create([User(**item) for item in items], update_fields=['user_id','login_name','user_name','password','email'], unique_fields=['user_id','login_name'], ignore_conflicts=True)
        return DetailResponse(msg=f"migrate data success {len(user_list)}")
    
    @action(detail=False, methods=['post'],url_path='interfaceList')
    def interface_list(self, request):
        interface_ids = request.data.get('interface_ids','')
        update_type = request.data.get('update_type')
        update_list =[]
        if(update_type == '1'):
            # 更新接口平台
            items = handler.query("select id,platform_name as name,platform_desc as `desc` from etlctl.etl_report_platform")
            platform_list = PlatformInfo.objects.bulk_create([PlatformInfo(**item) for item in items], update_conflicts=True, update_fields=["name","desc","update_datetime"], unique_fields=['id'])
            # 更新接口模块
            items = handler.query("select id,module_name as name,module_desc as `desc`,platform_id from etlctl.etl_report_module")
            module_list = ModuleInfo.objects.bulk_create([ModuleInfo(**item) for item in items], update_conflicts=True, update_fields=["name","desc","platform_id","update_datetime"], unique_fields=['id'])
            # 更新报表名称
            items = handler.query("select id,report_name as name,report_desc as `desc`,module_id from etlctl.etl_report_info")
            report_list = ReportInfo.objects.bulk_create([ReportInfo(**item) for item in items], update_conflicts=True, update_fields=["name","desc","module_id","update_datetime"], unique_fields=['id'])
            #更新接口
            items = handler.query(f'select * from ({table_sql.SQL_REPORT_INTERFACE}) t where id in ({interface_ids})' if interface_ids !='' else table_sql.SQL_REPORT_INTERFACE )
            update_list = ReportInterface.objects.bulk_create([ReportInterface(**item) for item in items],update_conflicts=True, update_fields=['interface_code','interface_name','interface_desc','interface_sql','module_name','report_name','interface_db_name','interface_db_type','is_login_visit','is_date_option','is_paging','is_second_table','is_total','alarm_type','update_datetime'], batch_size=1000, unique_fields=['id'])

        if(update_type == '2'):
            res = InterfaceFields.objects.filter(interface_id = int(interface_ids)).delete()
            items = handler.query( f'select * from ({table_sql.SQL_REPORT_INTERFACE_PARA}) t where interface_id in ({interface_ids})' if interface_ids !='' else table_sql.SQL_REPORT_INTERFACE_PARA )
            update_fields = [field.name for field in InterfaceFields._meta.get_fields()]
            update_fields.remove("id")
            update_list = InterfaceFields.objects.bulk_create([InterfaceFields(**item) for item in items], update_conflicts=True ,update_fields=update_fields, batch_size=1000, unique_fields=['id'])
        return DetailResponse(msg=f'update {"interface_ids:"+interface_ids if update_type == "1" else "fields"}, success size: {len(update_list)}')
    
    @action(detail=False, methods=['post'],url_path='datasourceList')
    def datasource_list(self, request):
        items = handler.query("select id,db_name,db_type,db_server,db_port,db_user,db_pwd from etlctl.etl_ctl_data_conn")
        insert_list = DataSource.objects.bulk_create([DataSource(**item) for item in items], update_conflicts=True, update_fields=['db_name','db_type','db_server','db_port','db_user','db_pwd'])
        q_condition = Q(datasource_name__isnull=True) | Q(datasource_name='')
        DataSource.objects.filter(q_condition).update(datasource_name=F('db_name'))
        return DetailResponse(msg=f"migrate data success {len(insert_list)}")
    
    @action(detail=False, methods=['post'],url_path='tableList')
    def table_list(self, request):
        items = handler.query("select id,table_name, table_schema, table_comment as table_desc, schema_type from etlctl.etl_ctl_table_relation")
        table_list = DataTable.objects.bulk_create([DataTable(**item) for item in items], update_conflicts=True, update_fields=['table_name','table_schema','table_desc','schema_type','update_datetime'])
        return DetailResponse(msg=f"migrate data success {len(table_list)}")
    
    @action(detail=False, methods=['post'],url_path='tableColumnList')
    def table_column_list(self, request):
        items = handler.query("select id,table_name,table_schema ,schema_type ,column_name as name,column_comment as `desc`,column_type as data_type,is_nullable,column_key ,ordinal_position as column_order from etlctl.etl_ctl_column_relation")
        column_list = TableColumn.objects.bulk_create([TableColumn(id=item["id"],
                                                                   table_id=DataTable.objects.get(Q(table_name=item["table_name"]),Q(table_schema=item["table_schema"]),Q(schema_type=item["schema_type"])).id,
                                                                   name= item["name"],
                                                                   desc=item["desc"],
                                                                   data_type=item["data_type"],
                                                                   column_order=item["column_order"],
                                                                   is_nullable = 1 if item["is_nullable"]=='YES' else 0,
                                                                   is_primary_key = 1 if item["column_key"] =='PRI' else None
                                                                ) for item in items],
                                                                     update_conflicts=True, 
                                                                     update_fields=['table_id','data_type','data_length','is_nullable','is_primary_key','name','desc','column_order'],
                                                                     batch_size=1000)
        return DetailResponse(msg=f"migrate data success, {len(column_list)}")

    @action(detail=False, methods=['post'],url_path='tableMappingList')
    def table_mapping_list(self, request):
        items = handler.query("select id,src_table_name,src_table_schema,src_schema_type,table_name,table_schema,schema_type from etlctl.etl_ctl_table_relation")
        table_mapping_list = TableMapping.objects.bulk_create([TableMapping(**item) for item in items], update_conflicts=True, update_fields=['src_table_name','src_table_schema','src_schema_type','table_name','table_schema','schema_type'])
        return DetailResponse(msg=f"migrate data success {len(table_mapping_list)}")


    
